package dao.impl;

import dao.UserDAO;
import pojo.User;
import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by zyb on 14-12-11.
 */
public class UserDAOJdbcImpl implements UserDAO {
    @Override
    public List<User> findAll() throws Exception {
        String queryStr = "select * from user";
        Connection connection = DBUtil.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(queryStr);
        List<User> userList = new ArrayList<User>();
        while (resultSet.next()){
            User user = new User();
            user.setId(Integer.valueOf(resultSet.getInt("id")));
            user.setAge(Integer.valueOf(resultSet.getInt("age")));
            user.setGender(resultSet.getString("gender"));
            user.setPhone(resultSet.getString("phone"));
            user.setUsername(resultSet.getString("username"));
            userList.add(user);
        }
        DBUtil.close(connection);
        return userList;
    }

    @Override
    public User findById(Integer id) throws Exception {
        String queryStr = "select * from user where id=?";
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(queryStr);
        preparedStatement.setInt(1,id.intValue());
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = new User();
        while (resultSet.next()){
            user.setId(Integer.valueOf(resultSet.getInt("id")));
            user.setAge(Integer.valueOf(resultSet.getInt("age")));
            user.setGender(resultSet.getString("gender"));
            user.setPhone(resultSet.getString("phone"));
            user.setUsername(resultSet.getString("username"));
        }
        DBUtil.close(connection);
        return user;
    }

    @Override
    public void save(User user) throws Exception {
        String saveStr = "insert into user(username,age,gender,phone) values(?,?,?,?)";
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(saveStr);
        preparedStatement.setString(1,user.getUsername());
        preparedStatement.setInt(2, user.getAge().intValue());
        preparedStatement.setString(3, user.getGender());
        preparedStatement.setString(4,user.getPhone());
        preparedStatement.executeUpdate();
        DBUtil.close(connection);
    }

    @Override
    public void delete(Integer id) throws Exception {
        String deleteStr = "delete from user where id=?";
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(deleteStr);
        preparedStatement.setInt(1,id.intValue());
        preparedStatement.executeUpdate();
        DBUtil.close(connection);
    }

    @Override
    public void update(User user) throws Exception {
        String updateStr = "update user set username=?,age=?,gender=?,phone=? where id=?";
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(updateStr);
        preparedStatement.setString(1,user.getUsername());
        preparedStatement.setInt(2, user.getAge().intValue());
        preparedStatement.setString(3, user.getGender());
        preparedStatement.setString(4,user.getPhone());
        preparedStatement.setInt(5,user.getId().intValue());
        preparedStatement.executeUpdate();
        DBUtil.close(connection);
    }
}
